﻿using EFramework.DataAccess.Entity;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;

namespace EFramework.DataAccess
{
    public class CommandSqlServer : CommandBase, ICommand
    {
        /// <summary>
        /// 数据连接池  SqlConnection
        /// </summary>
        private SqlConnection _conn;
        private SqlCommand _cmd;

        #region 构造函数
        private CommandSqlServer() { }
        internal CommandSqlServer(EConnection conn)
        {
            this.E_Conn = conn;

            if (E_Parameters == null)
            {
                E_Parameters = new List<ParameterItem>();
            }
        }
        #endregion

        #region 参数设置 [SetParamter] [SetOutParameter] [SetReplaceParamter]
        /// <summary>
        /// 设置输入参数
        /// </summary>
        /// <param name="name"></param>
        /// <param name="value"></param>
        public void SetParamter(string name, object value)
        {
            if (value == null || value.ToString() == default(DateTime).ToString())
            {
                value = DBNull.Value;
            }

            E_Parameters.Add(new ParameterItem()
            {
                ParameterName = name,
                ParameterValue = value
            });
        }

        /// <summary>
        /// 设置输出参数
        /// </summary>
        /// <param name="name"></param>
        /// <param name="dbType"></param>
        /// <param name="size"></param>
        public void SetOutParameter(string name, System.Data.DbType dbType, int? size)
        {
            E_Parameters.Add(new ParameterItem()
            {
                ParameterName = name,
                Direction = ParameterDirection.Output,
                DbType = dbType,
                Size = size
            });
        }

        /// <summary>
        /// 设置替换参数（需要先检查sql安全性）
        /// </summary>
        /// <param name="name"></param>
        /// <param name="value"></param>
        public void SetReplaceParamter(string name, string value)
        {
            //TODO 这里在替换之前要做注入检查
            E_Conn.SqlScript =
                 !string.IsNullOrWhiteSpace(E_Conn.SqlScript)
                 && E_Conn.SqlScript.Contains(name)
                 ? E_Conn.SqlScript.Replace(name, value)
                 : E_Conn.SqlScript;
        }
        #endregion

        #region 获取输出参数返回值
        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="name"></param>
        /// <returns></returns>
        public T GetOutParameter<T>(string name)
        {
            return (T)Convert.ChangeType(this._cmd.Parameters[name].Value, typeof(T));
        }
        #endregion

        public int ExecuteNonQuery()
        {
            int rows = 0;
            try
            {
                this.OpenConn();
                this.CreateSqlCommand();
                Utils.SqlServer.AppendParamters(this._cmd, E_Parameters);

                rows = this._cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw new Exception(Utils.SqlServer.BuilderSqlError(this.E_Conn, ex));
            }
            finally
            {
                this.CloseConn();
                this.DisposeConn();
            }
            return rows;
        }

        public object ExecuteSingle()
        {
            object obj;
            try
            {
                this.OpenConn();
                this.CreateSqlCommand();
                Utils.SqlServer.AppendParamters(this._cmd, E_Parameters);
                obj = this._cmd.ExecuteScalar();
            }
            catch (Exception ex)
            {
                throw new Exception(Utils.SqlServer.BuilderSqlError(this.E_Conn, ex));
            }
            finally
            {
                this.CloseConn();
                this.DisposeConn();
            }
            return obj;
        }

        public T ExecuteSingle<T>()
        {
            object obj = ExecuteSingle();
            if (obj == null)
            {
                return default(T);
            }
            return (T)(Convert.ChangeType(obj, typeof(T)));
        }

        public T ExecuteEntity<T>()
        {
            throw new NotImplementedException();
        }

        public IList<T> ExecuteEntities<T>()
        {
            throw new NotImplementedException();
        }

        public System.Data.DataSet ExecuteDataSet()
        {
            throw new NotImplementedException();
        }



        #region 私有方法
        #region 连接池 打开/关闭/销毁
        private void OpenConn()
        {
            if (_conn == null)
            {
                _conn = new SqlConnection(this.E_Conn.ConneString);
                _conn.Open();
            }
            else
            {
                switch (_conn.State)
                {
                    case ConnectionState.Broken:
                        _conn.Close();
                        _conn.Open();
                        break;
                    case ConnectionState.Closed:
                        _conn.Open();
                        break;
                    case ConnectionState.Connecting:
                        break;
                    case ConnectionState.Executing:
                        break;
                    case ConnectionState.Fetching:
                        break;
                    case ConnectionState.Open:
                        break;
                    default:
                        break;
                }
            }
        }
        private void CloseConn()
        {
            if (_conn != null)
            {
                if (this._conn.State != ConnectionState.Closed)
                {
                    _conn.Close();
                }
            }
        }
        private void DisposeConn()
        {
            if (_conn != null)
            {
                switch (this._conn.State)
                {
                    case ConnectionState.Open:
                    case ConnectionState.Broken:
                    case ConnectionState.Connecting:
                    case ConnectionState.Executing:
                    case ConnectionState.Fetching:
                        _conn.Close();
                        break;
                    case ConnectionState.Closed:
                    default:
                        break;
                }
                _conn.Dispose();
                _conn = null;
            }
        }
        private void CreateSqlCommand()
        {
            if (this._cmd == null)
            {
                this._cmd = new SqlCommand(this.E_Conn.SqlScript, this._conn);
            }
        }
        #endregion
        #endregion

        public override DbConnection SetDbConnection
        {
            get { return new SqlConnection(); }
        }
    }
}
